<?php

class ReservationDB {
    
    private static function getReservationFromPDO($result) {//works
        $reservation = new Reservation();
        $reservation->setReservation_id($result->reservation_id);
        $reservation->setReservation_note($result->reservation_note);
        $reservation->setReservation_timestamp($result->reservation_timestamp);
        $reservation->setResource_id($result->resource_id);
        $reservation->setResource_type_id($result->resource_type_id);
        $reservation->setSlot_date($result->slot_date);
        $reservation->setSlot_id($result->slot_id);
        $reservation->setSlots_group_id($result->slots_group_id);
        $reservation->setUser_id($result->user_id);
        return $reservation;
    }

    public static function getAllReservations() {
        try {
            $query = "SELECT * FROM reservation";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);
            $stmt->execute();
            $rowsAffected = $stmt->rowCount();
            if ($rowsAffected > 0) {
                $result = $stmt->fetchAll(PDO::FETCH_OBJ);
                $inventory = new Inventory();
                foreach ($result as $item) {
                    $reservation = self::getReservationFromPDO($item);
                    $inventory->attach($reservation, $reservation->getReservation_id());
                }
                return $inventory;
            } else {
                return null;
            }
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }
    
      public static function getReservationsByResourceTypeID($resource_type_id) {
        try {
            $query = "SELECT * FROM reservation WHERE resource_type_id = :resource_type_id";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);
            
            $stmt->bindParam('resource_type_id', $resource_type_id);
            
            $stmt->execute();
            $rowsAffected = $stmt->rowCount();
            if ($rowsAffected > 0) {
                $result = $stmt->fetchAll(PDO::FETCH_OBJ);
                $inventory = new Inventory();
                foreach ($result as $item) {
                    $reservation = self::getReservationFromPDO($item);
                    $inventory->attach($reservation, $reservation->getReservation_id());
                }
                return $inventory;
            } else {
                return null;
            }
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }

    public static function getReservationByID($reservation_id) {//works
        try {
            $query = "SELECT * FROM reservation WHERE reservation_id = :reservation_id";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);
            $stmt->bindParam(':reservation_id', $reservation_id);
            $stmt->execute();
            $rowsAffect = $stmt->rowCount();
            if ($rowsAffect > 0) {
                $result = $stmt->fetch(PDO::FETCH_OBJ);
                return self::getReservationFromPDO($result);
            } else {
                return null; //user does not exists
            }
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }

    public static function getReservationsBySlotsGroupID($slots_group_id) {//works
        try {
            $query = "SELECT * FROM reservation WHERE slots_group_id = :slots_group_id";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);
            $stmt->bindParam(':slots_group_id', $slots_group_id);
            $stmt->execute();
            $rowsAffect = $stmt->rowCount();
            if ($rowsAffect > 0) {
                $result = $stmt->fetch(PDO::FETCH_OBJ);
                return self::getReservationFromPDO($result);
            } else {
                return null; //user does not exists
            }
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }

    public static function updateReservation(Reservation $reservation) {
        try {
            $query = "UPDATE reservation SET 
                slot_id = :slot_id, 
                slot_date = :slot_date,
                reservation_note = :reservation_note 
                WHERE user_id = :user_id AND 
                resource_id = :resource_id AND
                slot_id = :slot_id";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);

            $slot_id = $reservation->getSlot_id();
            $slot_date = $reservation->getSlot_date();
            $reservation_note = $reservation->getReservation_note();
            $user_id = $reservation->getUser_id();
            $resource_id = $reservation->getResource_id();
            
            $stmt->bindParam(':slot_id', $slot_id);
            $stmt->bindParam(':slot_date', $slot_date);
            $stmt->bindParam(':reservation_note', $reservation_note);
            $stmt->bindParam(':user_id', $user_id);
            $stmt->bindParam(':resource_id', $resource_id);
            $stmt->execute();
            $rowsAffected = $stmt->rowCount();
            return $rowsAffected;
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }

    public static function deleteReservation($reservation_id) {//works
        try {
            $query = "DELETE FROM reservation WHERE reservation_id = :reservation_id";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);
            $stmt->bindParam(':reservation_id', $reservation_id);
            $stmt->execute();
            $rowsAffected = $stmt->rowCount();
            return $rowsAffected;
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }

    public static function insertReservation(Reservation $reservation) {//works
        try {
            $query = "INSERT INTO reservation SET
                reservation_id = :reservation_id,
                user_id = :user_id,
                resource_id = :resource_id,
                slot_id = :slot_id, 
                slot_date = :slot_date,
                resource_type_id = :resource_type_id,
                slots_group_id = :slots_group_id,
                reservation_timestamp = :reservation_timestamp,
                reservation_note = :reservation_note";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);

            $reservation_id = $reservation->getReservation_id();
            $resource_type_id = $reservation->getResource_type_id();
            $slots_group_id = $reservation->getSlots_group_id();
            $reservation_timestamp = $reservation->getReservation_timestamp();
            $slot_id = $reservation->getSlot_id();
            $slot_date = $reservation->getSlot_date();
            $reservation_note = $reservation->getReservation_note();
            $user_id = $reservation->getUser_id();
            $resource_id = $reservation->getResource_id();
            
            $stmt->bindParam(':reservation_id', $reservation_id);
            $stmt->bindParam(':slots_group_id', $slots_group_id);
            $stmt->bindParam(':resource_type_id', $resource_type_id);
            $stmt->bindParam(':reservation_timestamp', $reservation_timestamp);
            $stmt->bindParam(':slot_id', $slot_id);
            $stmt->bindParam(':slot_date', $slot_date);
            $stmt->bindParam(':reservation_note', $reservation_note);
            $stmt->bindParam(':user_id', $user_id);
            $stmt->bindParam(':resource_id', $resource_id);
            $stmt->execute();
            
            $rowsAffected = $stmt->rowCount();
            return $rowsAffected;
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }

    
    public static function getCountReservedSlotIDForResourceType($slot_date, $slot_id, $resource_type_id) {//works
        try {
            $query = "SELECT COUNT(*) as count FROM reservation WHERE slot_date = :slot_date
                AND slot_id = :slot_id AND resource_type_id = :resource_type_id";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);
            $stmt->bindParam(':slot_date', $slot_date);
            $stmt->bindParam(':slot_id', $slot_id); 
            $stmt->bindParam(':resource_type_id', $resource_type_id);
            $stmt->execute();
            $recordsCount = $stmt->fetchColumn();
            return $recordsCount;
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }
    public static function getReservedSlotCountForResourceType($from_date, $to_date, $resource_type_id) {//works
        try {
            $query = "SELECT COUNT(*) as count FROM reservation WHERE resource_type_id = :resource_type_id AND slot_date BETWEEN :from_date AND :to_date";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);
            $stmt->bindParam(':from_date', $from_date);
            $stmt->bindParam(':to_date', $to_date); 
            $stmt->bindParam(':resource_type_id', $resource_type_id);
            $stmt->execute();
            $recordsCount = $stmt->fetchColumn();
            return $recordsCount;
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }
    
    
    public static function getReservedResourceIDs($slot_date, $slot_id, $resource_type_id) {//works
        try {
            $query = "SELECT resource_id FROM reservation WHERE slot_date = :slot_date
                AND slot_id = :slot_id AND resource_type_id = :resource_type_id";
            $dbh = DatabaseConnection::getDB();
            $stmt = $dbh->prepare($query);
            $stmt->bindParam(':slot_date', $slot_date);
            $stmt->bindParam(':slot_id', $slot_id); 
            $stmt->bindParam(':resource_type_id', $resource_type_id);
            $stmt->execute();
            $resources_ids = $stmt->fetchAll();
            return $resources_ids;
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
    }
    
    
    public static function getResourceTypeReservationsCount($resource_type_id) {
        $query = "SELECT COUNT(*) as count From reservation WHERE resource_type_id = :resource_type_id";
        $dbh = DatabaseConnection::getDB();
        $stmt = $dbh->prepare($query);
        
        $resource_type_id = $resource_type_id;
        $stmt->bindParam(':resource_type_id', $resource_type_id);
            
        $stmt->execute();
        $recordsCount = $stmt->fetchColumn();
        return $recordsCount;
    }
    

}

?>
